﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using XiAnAirport.BUS;
using XiAnAirport.Models;
using static XiAnAirport.Models.MqttDataInfo;

namespace XiAnAirport.DAL
{
    public class MqttDataDAL
    {
        public bool CheckData(string manufacturer, string manufacturerId, string dataType, string topicType)
        {
            try
            {
                string strsql = string.Format("select COUNT(1) FROM mqttdata WHERE dataType='{0}' AND manufacturer='{1}' AND manufacturerId='{2}' AND topicType='{3}'", dataType, manufacturer, manufacturerId, topicType);
                int count = SqlHelper<mqttdata>.SqlScalar(strsql);
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }

        public string AddMqttData(mqttdata mqttDataInfo)
        {
            string strSql = string.Format("INSERT INTO mqttdata (dataType,manufacturer,manufacturerId,dataNum,topicType,updateTime)VALUES('{0}','{1}','{2}',{3},'{4}','{5}')"
                , mqttDataInfo.dataType, mqttDataInfo.manufacturer, mqttDataInfo.manufacturerId, mqttDataInfo.dataNum, mqttDataInfo.topicType, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            try
            {
                int count = SqlHelper<mqttdata>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateMqttData(mqttdata mqttDataInfo)
        {
            string strSql = string.Format("UPDATE mqttdata SET dataNum={0},updateTime='{5}' WHERE manufacturer='{1}' AND manufacturerId={2} AND dataType='{3}' AND topicType='{4}'"
                , mqttDataInfo.dataNum, mqttDataInfo.manufacturer, mqttDataInfo.manufacturerId, mqttDataInfo.dataType, mqttDataInfo.topicType, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            try
            {
                int count = SqlHelper<mqttdata>.SqlExecuteRows(strSql);

                if (count >= 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<FaultRecordReport> QueryFaultRecordReportList()
        {
            string strSql = "SELECT DISTINCT elevatorName,elevatorNo,internalNo,elevatorType,areaName,faultContent,faultType,faultCode,faultTime FROM mqtt_faultrecord WHERE DATE(faultTime)='" + DateTime.Now.Date.ToString() + "' ORDER BY faultTime DESC LIMIT 0,100";
            return SqlHelper<FaultRecordReport>.SqlList(strSql);
        }
        public FaultRecordReport LoadFaultRecordReport(int id)
        {
            string strSql = "SELECT * FROM mqtt_faultrecord WHERE id=" + id;
            return SqlHelper<FaultRecordReport>.SqlSingleData(strSql);
        }

        public List<FaultRecordReport> QueryFaultRecordReport(string elevatorNo, string internalNo)
        {
            string strSql = "SELECT * FROM mqtt_faultrecord WHERE elevatorNo='" + elevatorNo + "' AND internalNo='" + internalNo + "'";
            return SqlHelper<FaultRecordReport>.SqlList(strSql);
        }

        public List<FaultRecordReport> QueryKoneFaultReportList()
        {
            string strSql = "SELECT DISTINCT vi_fault.faultTime,vi_fault.elevatorID,vi_fault.faultCode AS faultContent,area.areaName AS mainAreaName,vi_fault.areaName,vi_fault.faultTime,vi_fault.elevatorName  FROM vi_fault LEFT JOIN area ON vi_fault.mainAreaId=area.id WHERE vi_fault.IsRead=1 ORDER BY faultTime desc LIMIT 0,100";
            return SqlHelper<FaultRecordReport>.SqlList(strSql);
        }

        public List<mqttdata> QueryMqttdata(string topicType, string manufacturerId, string manufacturer)
        {
            string strSql = string.Format("select * FROM mqttdata WHERE topicType='{0}' AND manufacturer='{1}' AND manufacturerId='{2}'", topicType, manufacturer, manufacturerId);
            return SqlHelper<mqttdata>.SqlList(strSql);
        }

        public TodayRuntimeData QueryRuntime()
        {
            string strSql = string.Format("SELECT SUM(dataNum) AS runtime FROM mqttdata WHERE topicType='TodayRuntime'");
            return SqlHelper<TodayRuntimeData>.SqlSingleData(strSql);
        }

        public List<mqttdata> QueryManufactyreDeviceNum(string manufacturerId, string manufacturer)
        {
            string strSql = string.Format("select * FROM mqttdata WHERE topicType='DeviceStatusTotal' AND manufacturer='{0}' AND manufacturerId='{1}' AND(dataType='normalTotal' OR dataType='deviceTotal')", manufacturer, manufacturerId);
            return SqlHelper<mqttdata>.SqlList(strSql);
        }

        public List<mqttdata> QueryTodayProcess()
        {
            string strSql = string.Format("select SUM(dataNum) AS dataNum,dataType FROM mqttdata WHERE topicType='TodayProcess' GROUP BY dataType");
            return SqlHelper<mqttdata>.SqlList(strSql);
        }

        public List<mqttdata> QueryKoneTodayProcess()
        {
            string strSql = string.Format("select * FROM mqttdata WHERE topicType='TodayProcess' AND manufacturer='KONE'");
            return SqlHelper<mqttdata>.SqlList(strSql);
        }

        public string AddFaultRecordData(FaultRecordReport faultRecordReport)
        {
            string strSql = string.Format("INSERT INTO mqtt_faultrecord(updateTime,elevatorName,elevatorNo,internalNo,elevatorType,areaName,faultContent,faultType,faultCode,faultReason,faultTime,manufacturer)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')"
                , DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), faultRecordReport.elevatorName, faultRecordReport.elevatorNo, faultRecordReport.internalNo, faultRecordReport.elevatorType, faultRecordReport.areaName, faultRecordReport.faultContent, faultRecordReport.faultType, faultRecordReport.faultCode, faultRecordReport.faultReason, faultRecordReport.faultTime, faultRecordReport.manufacturer);
            try
            {
                int count = SqlHelper<FaultRecordReport>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string UpdateFaultRepairData(FaultRecordReport faultRecordReport)
        {
            string strSql = string.Format("UPDATE mqtt_faultrecord SET faultCode='{0}' AND faultEndTime='{1}' WHERE elevatorName='{2}' AND elevatorNo='{3}' AND internalNo='{4}' AND faultContent='{5}' AND faultType='{6}' AND faultReason='{7}' AND faultEndTime IS NULL"
              , faultRecordReport.faultCode, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), faultRecordReport.elevatorName, faultRecordReport.elevatorNo, faultRecordReport.internalNo, faultRecordReport.faultContent, faultRecordReport.faultType, faultRecordReport.faultReason);
            try
            {
                int count = SqlHelper<FaultRecordReport>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<Notification> QueryNotificationList()
        {
            string strSql = "SELECT * FROM notification WHERE flag=1";
            return SqlHelper<Notification>.SqlList(strSql);
        }

        public List<devicevalue> QueryDeviceInfo()
        {
            string strSql = "select SUM(dataNum) AS dataNum,dataType FROM mqttdata WHERE topicType='DeviceStatusTotal' GROUP BY dataType";
            return SqlHelper<devicevalue>.SqlList(strSql);
        }

        public List<mqttdata> QueryKoneDeviceInfo()
        {
            string strSql = "select * FROM mqttdata WHERE topicType='DeviceStatusTotal' AND manufacturer='KONE'";
            return SqlHelper<mqttdata>.SqlList(strSql);
        }

        public int QueryKoneRuns()
        {
            string strSql = "SELECT SUM(runs) FROM elevator_runs LEFT JOIN elevator ON elevator.id=elevator_runs.elevatorId WHERE elevator.isDelete=0";
            return SqlHelper<devicevalue>.SqlScalar(strSql);
        }
        public long QueryKoneRunTime()
        {
            string strSql = "SELECT SUM(runTime) FROM elevator_runstimes LEFT JOIN elevator ON elevator.id=elevator_runstimes.elevatorId WHERE elevator.isDelete=0";
            return SqlHelper<devicevalue>.SqlScalar(strSql);
        }
        public int QueryKoneFault()
        {
            string strSql = "SELECT dataNum FROM mqttdata WHERE manufacturer='KONE' AND topicType='FaultStatistics' AND dataType='faultTotal'";
            return SqlHelper<devicevalue>.SqlScalar(strSql);
        }

        public List<AreaDeviceNum> QueryElevatorCount()
        {
            string strSql = @"SELECT
	                            a.id,
	                            a.areaName,
	                            a.mainAreaId,
	                            (
		                            SELECT
			                            COUNT(1)
		                            FROM
			                            elevator
		                            WHERE
			                            isDelete = 0
		                            AND outofservice = 0
		                            AND areaId = a.id
	                            ) AS DeviceNum
                            FROM
	                            area a
                            WHERE
	                            a.isDelete = 0";
            return SqlHelper<AreaDeviceNum>.SqlList(strSql);
        }

        public int QueryDeviceTotal()
        {
            string strsql = "select COUNT(1) FROM elevator";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public bool ClearKoneDeviceTotal()
        {
            string strsql = "DELETE FROM mqttdata WHERE topicType='DeviceStatusTotal' AND manufacturerId='04' AND manufacturer='KONE'";
            return SqlHelper<Elevator>.SqlDelete(strsql);
        }

        public int QueryTodayFault()
        {
            string strsql = "select COUNT(1) FROM vi_fault WHERE DATE(faultTime)=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') where IsRead=1";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryInCompletelRepairOrder()
        {
            string strsql = "select COUNT(1) FROM vi_repairorderinfo WHERE DATE(preparationTime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND workorderStateID!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryInCompletelFault()
        {
            string strsql = "select COUNT(1) FROM vi_fault WHERE DATE(faultTime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND faultEndTime IS NULL";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryMaintainOrder()
        {
            string strsql = "select COUNT(1) FROM vi_maintaininfo WHERE DATE(maintenanceDatetime)=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }
        public int QueryRepairOrder()
        {
            string strsql = "select COUNT(1) FROM vi_repairorderinfo WHERE DATE(preparationTime)=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryInCompleteMaintainOrder()
        {
            string strsql = "select COUNT(1) FROM vi_maintaininfo WHERE DATE(maintenanceDatetime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND maintenancestateId!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryInspectionOrder()
        {
            string strsql = "select COUNT(1) FROM vi_inspectioninfo WHERE DATE(inspectionDatetime)=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryInCompleteInspectionOrder()
        {
            string strsql = "select COUNT(1) FROM vi_inspectioninfo WHERE DATE(inspectionDatetime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND inspectionStateId!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryTotalFault()
        {
            string strsql = "select COUNT(1) FROM vi_fault where IsRead=1";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryAllInCompleteMaintainOrder()
        {
            string strsql = "select COUNT(1) FROM vi_maintaininfo WHERE DATE(maintenanceDatetime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND maintenancestateId!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryUntreatedMaintainOrder()
        {
            string strsql = "select COUNT(1) FROM vi_maintaininfo WHERE DATE(maintenanceDatetime)<DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND maintenancestateId!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryAllInCompleteInspectionOrder()
        {
            string strsql = "select COUNT(1) FROM vi_inspectioninfo WHERE DATE(inspectionDatetime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND inspectionStateId!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }
        public int QueryUntreatedInspectionOrder()
        {
            string strsql = "select COUNT(1) FROM vi_inspectioninfo WHERE DATE(inspectionDatetime)<DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND inspectionStateId!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryAllInCompleteFault()
        {
            string strsql = "select COUNT(1) FROM vi_fault WHERE DATE(faultTime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND faultEndTime IS NULL";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }
        //public int QueryUntreatedFault()
        //{
        //    string strsql = "select COUNT(1) FROM vi_fault WHERE DATE(faultTime)<DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND faultEndTime IS NULL";
        //    return SqlHelper<Elevator>.SqlScalar(strsql);
        //}

        public int QueryUntreatedFault()
        {
            string strsql = "select COUNT(1) FROM vi_repairorderinfo WHERE DATE(preparationTime)<DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND workorderStateID!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryAllInCompletelRepairOrder()
        {
            string strsql = "select COUNT(1) FROM vi_repairorderinfo WHERE DATE(preparationTime)<=DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND workorderStateID!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public int QueryUntreatedRepairOrder()
        {
            string strsql = "select COUNT(1) FROM vi_repairorderinfo WHERE DATE(preparationTime)<DATE('" + DateTime.Now.ToString("yyyy-MM-dd") + "') AND workorderStateID!=4";
            return SqlHelper<Elevator>.SqlScalar(strsql);
        }

        public bool UpdateElevatorBend()
        {
            string strsql = "UPDATE elevator set warningtime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE bend>bendThreshold*0.9 AND bendThreshold>0 AND warningtime IS NULL";
            return SqlHelper<Elevator>.SqlExecute(strsql);
        }

        public bool DeleteDistinctMqttFaultrecord()
        {
            try
            {
                string strsql = @"DELETE t1 
                                    FROM
	                                    mqtt_faultrecord t1
	                                    JOIN mqtt_faultrecord t2 ON t1.elevatorNo = t2.elevatorNo AND t1.faultTime=t2.faultTime
	                                    AND t1.id > t2.id";
                return SqlHelper<Elevator>.SqlExecute(strsql);
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteDistinctFaultReport()
        {
            try
            {
                string strsql = @"DELETE t1 
                                FROM
	                                fault_report t1
	                                JOIN fault_report t2 ON t1.elevatorID = t2.elevatorID AND t1.faultTime=t2.faultTime
	                                AND t1.id > t2.id";
                return SqlHelper<Elevator>.SqlExecute(strsql);
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteDistinctRepairWorkorder()
        {
            try
            {
                string strsql = @"DELETE t1 
                                FROM
	                                repair_workorder t1
	                                JOIN repair_workorder t2 ON t1.elevatorID = t2.elevatorID AND t1.reportTime=t2.reportTime
	                                AND t1.id > t2.id";
                return SqlHelper<Elevator>.SqlExecute(strsql);
            }
            catch
            {
                return false;
            }
        }
    }
}
